# coding=gb2312
import importlib
import os
import sys
import re

def course_ifdentify():
    return {"phonics":1,
            "practical_1":2,
            "practical_4":3,
            "junior":4,
            "high_school":5,
            "cet4":6,
            "tem4":7,
            "cet6":8,
            "ielts":9,
            "toefl":10,
            "gre":11,
            "sat":12,
            "practical_2":14,
            "practical_3":15,
    }

from openpyxl.reader.excel import load_workbook

import xlsx
from finder import fs

reload(sys)
sys.setdefaultencoding('utf-8')


def make_script(book_name):
    """"""
    wb = load_workbook(filename="xlsx/%s" % book_name)
    lines = []
    for shtName in wb.get_sheet_names():
        lines.append("source dml/%s/%s.sql;" % (book_name, shtName))
    fs.write("dml/%s.dml.sql" % book_name, lines)
    print("dml/%s.dml.sql" % book_name)


def process_dml(book_name):
    """"""
    if not os.path.exists("dml/%s" % book_name):
        os.makedirs("dml/%s" % book_name)
    wb = load_workbook(filename="xlsx/%s" %book_name, data_only=True)
    for shtName in wb.get_sheet_names():
        records = build_dml(wb, shtName)
        fs.write("dml/%s/%s.sql" % (book_name, shtName), records)
        print("%s.sql." %shtName)
        BookPrefix = re.split('\.', book_name)
        if shtName == BookPrefix[0] + '_story_word':      ###UPDATE tbl_group_pro set tbl_group_pro.serial="" where id=;
            update_data = update_story_serial(book_name,shtName)
            print(shtName)
            fs.write("dml/%s/update_%s.sql" % (book_name, shtName), update_data)


def prepare(value):
    return value.replace('\'', '\\\'').strip()


def build_dml(wb, sheet_name):
    """"""
    ws = wb.get_sheet_by_name(sheet_name)
    rtn = []
    mr = ws.max_row + 1
    mc = ws.max_column + 1
    if mr <= 2:
        return rtn

    line = "insert into %s (" % sheet_name
    for c in range(1, mc):
        value = str(ws.cell(row=1, column=c).value)
        if c == mc - 1:
            line = line + value + ") values"
        else:
            line = line + value + ", "
    rtn.append(line)

    for r in range(2, mr):
        line = "("
        for c in range(1, mc):
            cell = ws.cell(row=r, column=c)
            value = prepare(str(cell.value))
            if xlsx.is_non_wrap(ws, c):
                value = value
            else:
                if value == "None":
                    value = 'NULL'
                else:
                    value = "'" + value + "'"

            if c == mc - 1:
                line = line + value + ")"
            else:
                line = line + value + ", "
        if r == mr - 1:
            line = line + ";"
        else:
            line = line + ","
        rtn.append(line)
    return rtn


def update_story_serial(book_name,sheet_name):
    BookPrefix = re.split('\.',book_name)
    # if sheet_name == BookPrefix[0] + "_story_word":
    wb = load_workbook(filename="xlsx/%s" % book_name, data_only=True)
    ws = wb.get_sheet_by_name(sheet_name)
    mr = ws.max_row + 1
    mc = ws.max_column + 1
    group_id = 0   ###str(ws.cell(row=2, column=1).value)  ##story_id===>group_id
    # serial = str(ws.cell(row=1, column=1).value)  ##story_id===>group_id
    line = "UPDATE tbl_group_pro SET "
    rtn = []

    serial = []   ###str(ws.cell(row=2, column=2).value)
    for r in range(2, mr):
        if group_id == 0:
            group_id = str(ws.cell(row=r, column=1).value)
        cell_group_id = str(ws.cell(row=r, column=1).value)
        serial_value = str(ws.cell(row=r, column=2).value)
        if group_id == cell_group_id:
            serial.append(serial_value)
            # serial_value += "," + serial_value
        else:
            serial_value_serial = ",".join(serial)
            coursename_identify = course_ifdentify()
            course_groupPrefix = coursename_identify[BookPrefix[0]] * 100000 + int(group_id)
            line += "serial=\"" + serial_value_serial + "\"" + " where id=\"" + str(course_groupPrefix) + "\";"
            # print line
            serial = []
            serial.append(serial_value)
            # serial = serial_value
            group_id = str(ws.cell(row=r, column=1).value)
            rtn.append(line)
            line = "UPDATE tbl_group_pro SET "

        # if r == mr - 1:
        #     line = line + ";"
        # else:
        #     line = line + ","
    return rtn
